import requests
import json
import pymysql

response  = requests.get(url='http://43.143.30.32:9527/movies')
dataJson = json.loads(response.content)

# 创建一个字典来存储每个国家的票房总和
country_box_office = {}

# 初始化票房统计字典
rating_intervals = {
    "3.0-3.5": 0,
    "9.0-9.5": 0,
    "无评分": 0
}

# 遍历数据，累加每个国家的票房
for movie in dataJson['data']:
    country = movie['制片地区']
    box_office = int(movie['票房'])  # 票房数据需要转换为整数

    rating = movie['评分']

    if rating == '-':  # 无评分
        rating_intervals["无评分"] += box_office
    else:
        rating = float(rating)  # 将评分转换为浮点数
        if 3.0 <= rating <= 3.5:
            rating_intervals["3.0-3.5"] += box_office
        elif 9.0 <= rating <= 9.5:
            rating_intervals["9.0-9.5"] += box_office

    if country in country_box_office:
        country_box_office[country] += box_office
    else:
        country_box_office[country] = box_office
# 对国家按票房总和进行排序，取票房最高的三个国家
top_countries = sorted(country_box_office.items(), key=lambda x: x[1], reverse=True)[:3]

result = []
# 输出结果
for country, total_box_office in top_countries:
    result.append(["BigWatermelon",country,total_box_office])
    #print(f"国家: {country}, 票房总和: {total_box_office}")

# 输出结果
for interval, total_box_office in rating_intervals.items():
    result.append(["BigWatermelon", interval, total_box_office])
    #print(f"评分区间: {interval}, 票房总和: {total_box_office}")

print(result)

# mysql连接
connection = pymysql.connect(host='127.0.0.1', user='root', password='123456', db='rpa')
try:
    # 创建游标
    with connection.cursor() as cursor:
        # SQL语句
        sql = "INSERT INTO movie_json (submit_person,movie_information,movie_obx_office) VALUES (%s, %s, %s)"
        # 构造参数列表
        values = result
        # 执行批量插入操作
        cursor.executemany(sql, values)
    # 提交事务
    connection.commit()
finally:
    # 关闭连接
    connection.close()